package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import modeloDatos.Interes;

public class DAOIntereses {

	private Connection conn;
	
	public DAOIntereses() {
		
	}
	
	public void finalize(){
        ConnectionManager.getInstance().checkIn(conn);
    }
	
	public List<Interes> interesesDeClientes(Integer idC){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT i.idInteres, i.descripcion FROM intereses i, clienteinteres clii WHERE clii.idInteres=i.idInteres AND clii.idClienteEmpresa=?";
        ResultSet result = null;
        List<Interes> listaI = new LinkedList<Interes>();
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idC);
			result = stmt.executeQuery();
			while (result.next()) {
				Integer idI = result.getInt("idInteres");
				String d = result.getString("descripcion");
				listaI.add(new Interes(idI, d));
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaI;
	}
	
	public List<Interes> intereses(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM intereses";
        ResultSet result = null;
        List<Interes> listaI = new LinkedList<Interes>();
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				Integer idI = result.getInt("idInteres");
				String d = result.getString("descripcion");
				listaI.add(new Interes(idI, d));
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaI;
	}
	
	public Boolean borrarInteres(Integer idC, Integer idI){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "DELETE FROM clienteinteres WHERE idClienteEmpresa=? AND idInteres=?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idC);
			stmt.setInt(2, idI);
			stmt.executeUpdate();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return true;
	}
	
	public Boolean agregarInteres(Integer idC, Integer idI){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "INSERT INTO clienteinteres (idClienteEmpresa, idInteres) VALUES (?,?)";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idC);
			stmt.setInt(2, idI);
			stmt.executeUpdate();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return true;
	}
	
	public Boolean insertarInteres(String desc){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "INSERT INTO intereses (descripcion) VALUES (?)";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, desc);
			stmt.executeUpdate();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return true;
	}

}
